﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public static partial class DataManager
    {
        #region Public Methods

        #region Select

        public static DataTable GetOneMasterEneregyConsumptionById(int ItemId, int LanguageCode)
        {
            #region Stroed procedure
            _Command.CommandText = "SP_GetOneMasterEnergyConumptionById";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            // clear all previous paramaters
            _Command.Parameters.Clear();

            //// Add Language code Param
            //_Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            // Add Language code Param
            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

            _DataSet = new DataSet();

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                DataTable dtEnergy = new DataTable();
                dtEnergy.Columns.Add("ItemId", typeof(int));
                dtEnergy.Columns.Add("EnglishItemName", typeof(string));
                dtEnergy.Columns.Add("EnglishDescription", typeof(string));
                dtEnergy.Columns.Add("FrenchItemName", typeof(string));
                dtEnergy.Columns.Add("FrenchDescription", typeof(string));
                dtEnergy.Columns.Add("ArabicItemName", typeof(string));
                dtEnergy.Columns.Add("ArabicDescription", typeof(string));

                dtEnergy.Columns.Add("IsMajor", typeof(bool));
                dtEnergy.Columns.Add("ParentItemId", typeof(int));
                dtEnergy.Columns.Add("ParentItemName", typeof(string));
                dtEnergy.Columns.Add("LanguageCode", typeof(int));

                DataRow dr = dtEnergy.NewRow();
                try
                {
                    dr["ItemId"] = Convert.ToInt32(_DataSet.Tables[0].Rows[0]["ItemId"]);
                    if (_DataSet.Tables[0].Rows[0]["IsMajor"] != DBNull.Value)
                        dr["IsMajor"] = Convert.ToBoolean(_DataSet.Tables[0].Rows[0]["IsMajor"]);

                    // English
                    DataRow[] drLanguageData = _DataSet.Tables[0].Select("LanguageCode = 1033");
                    if (drLanguageData.Length > 0)
                    {
                        dr["EnglishItemName"] = Convert.ToString(drLanguageData[0]["ItemName"]);
                        if (drLanguageData[0]["Description"] != DBNull.Value)
                            dr["EnglishDescription"] = Convert.ToString(drLanguageData[0]["Description"]);
                    }

                    // French
                    drLanguageData = _DataSet.Tables[0].Select("LanguageCode = 1036");
                    if (drLanguageData.Length > 0)
                    {
                        dr["FrenchItemName"] = Convert.ToString(drLanguageData[0]["ItemName"]);
                        if (drLanguageData[0]["Description"] != DBNull.Value)
                            dr["FrenchDescription"] = Convert.ToString(drLanguageData[0]["Description"]);
                    }

                    // Arabic
                    drLanguageData = _DataSet.Tables[0].Select("LanguageCode = 1025");
                    if (drLanguageData.Length > 0)
                    {
                        dr["ArabicItemName"] = Convert.ToString(drLanguageData[0]["ItemName"]);
                        if (drLanguageData[0]["Description"] != DBNull.Value)
                            dr["ArabicDescription"] = Convert.ToString(drLanguageData[0]["Description"]);
                    }

                    // Current Language
                    dr["LanguageCode"] = LanguageCode;
                    drLanguageData = _DataSet.Tables[0].Select("LanguageCode =" + LanguageCode);
                    if (drLanguageData.Length > 0)
                    {
                        if (drLanguageData[0]["ParentItemId"] != DBNull.Value)
                            dr["ParentItemId"] = Convert.ToInt32(drLanguageData[0]["ParentItemId"]);

                        if (drLanguageData[0]["ParentItemName"] != DBNull.Value)
                            dr["ParentItemName"] = Convert.ToString(drLanguageData[0]["ParentItemName"]);
                    }
                }
                catch (Exception exc)
                { }
                dtEnergy.Rows.Add(dr);

                return dtEnergy;
            }
            else
                return null;

        }

        public static DataTable GetAllMasterEnergyConsumptions(int LanguageCode, int ExceptionItemId)
        {

            _Command.CommandText = "SP_GetAllMasterEnergyConumption";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
            _Command.Parameters.Add("@ExceptionItemId", SqlDbType.Int).Value = ExceptionItemId;

            _DataSet = new DataSet();

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return _DataSet.Tables[0];
            else
                return null;

        }

        public static EnergyConsumptionDevelopmentItemValue GetOneObjEneregyConumptionById(int ItemId, int CountryId)
        {
            #region Stroed procedure
            _Command.CommandText = "SP_GetEnergyConsumptionDevelopmentItemValues";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            // clear all previous paramaters
            _Command.Parameters.Clear();

            // Add Item Id Param
            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

            // Add Country Id Param
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            _DataSet = new DataSet();

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            EnergyConsumptionDevelopmentItemValue _energy = new EnergyConsumptionDevelopmentItemValue();
            _energy.ItemId = ItemId;
            _energy.CountryId = CountryId;

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                for (int columnIndex = 0; columnIndex < _DataSet.Tables[0].Columns.Count - 2; columnIndex++)
                {
                    ItemValue tempItemValue = new ItemValue();
                    tempItemValue.Year = Convert.ToInt32(_DataSet.Tables[0].Columns[columnIndex].ColumnName);
                    tempItemValue.Value = Convert.ToString(_DataSet.Tables[0].Rows[0][columnIndex]);
                    _energy.ItemValues.Add(tempItemValue);
                }
                _energy.ItemValues.Sort(delegate(ItemValue _item1, ItemValue _item2) { return _item1.Year.CompareTo(_item2.Year); });
            }
            else
            {
                for (int columnIndex = 0; columnIndex < _DataSet.Tables[0].Columns.Count - 2; columnIndex++)
                {
                    ItemValue tempItemValue = new ItemValue();
                    tempItemValue.Year = Convert.ToInt32(_DataSet.Tables[0].Columns[columnIndex].ColumnName);
                    tempItemValue.Value = "";
                    _energy.ItemValues.Add(tempItemValue);
                }
                _energy.ItemValues.Sort(delegate(ItemValue _item1, ItemValue _item2) { return _item1.Year.CompareTo(_item2.Year); });
            }
            return _energy;
        }

        public static DataTable GetOneEneregyConsumptionValuesById(int ItemId, int CountryId)
        {
            #region Stroed procedure
            _Command.CommandText = "SP_GetEnergyConsumptionDevelopmentItemValues";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            // clear all previous paramaters
            _Command.Parameters.Clear();

            // Add Item Id Param
            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

            // Add Country Id Param
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            _DataSet = new DataSet();

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet.Tables[0].Rows.Count > 0)
            {
                _DataSet.Tables[0].Columns.Remove("ItemId");
                _DataSet.Tables[0].Columns.Remove("COuntryId");
                return _DataSet.Tables[0];
            }
            else
            {
                DataTable dtEmpty = new DataTable();
                dtEmpty = _DataSet.Tables[0].Clone();
                dtEmpty.Columns.Remove("ItemId");
                dtEmpty.Columns.Remove("COuntryId");
                DataRow dr = dtEmpty.NewRow();

                for (int i = 0; i < dtEmpty.Columns.Count - 2; i++)
                {
                    dr[i] = "";
                }
                dtEmpty.Rows.Add(dr);
                return dtEmpty;
            }
        }

        public static DataTable GetAllEnergyConsumptionDevelopmentItemValues(int LanguageCode, int CountryId)
        {
            _Command.CommandText = "SP_GetAllEnergyConsumptionDevelopmentItemValues";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            _DataSet = new DataSet();

            _Adapter.SelectCommand = _Command;
            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
                return null;
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return _DataSet.Tables[0];
            else
                return null;

        }

        public static string GetSingleEnergyConsumptionItemValue(int ItemId, int CountryId, int Year)
        {
            _Command.CommandText = "SP_GetSingleEnergyConsumptionItemValue";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;
            _Command.Parameters.Add("@Year", SqlDbType.Int).Value = Year;

            _Adapter.SelectCommand = _Command;

            _DataSet.Tables.Clear();

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                CloseConnection();
            }
            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return Convert.ToString(_DataSet.Tables[0].Rows[0][0]);
            else
                return "";
        }

        #endregion

        #region Update

        public static bool UpdateMasterEnergyConsumptions(EnergyConsumptionDevelopmentItemValue _energy)
        {
            #region Stored procedure
            _Command.CommandText = "SP_UpdateMasterEnergyConsumptions";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Paramaters

            // Clear All previous parameters if exist
            _Command.Parameters.Clear();

            // ItemId
            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = _energy.ItemId;

            #region English Parameters

            // ItemName
            _Command.Parameters.Add("@EnglishItemName", SqlDbType.NVarChar).Value = _energy.EnglishItemName;
            // Description
            _Command.Parameters.Add("@EnglishDescription", SqlDbType.NVarChar).Value = _energy.EnglishDescription;

            #endregion

            #region French Parameters

            // ItemName
            _Command.Parameters.Add("@FrenchItemName", SqlDbType.NVarChar).Value = _energy.FrenchItemName;
            // Description
            _Command.Parameters.Add("@FrenchDescription", SqlDbType.NVarChar).Value = _energy.FrenchDescription;

            #endregion

            #region Arabic Parameters

            // ItemName
            _Command.Parameters.Add("@ArabicItemName", SqlDbType.NVarChar).Value = _energy.ArabicItemName;
            // Description
            _Command.Parameters.Add("@ArabicDescription", SqlDbType.NVarChar).Value = _energy.ArabicDescription;

            #endregion

            // ParentItemId
            if (_energy.ParentItemId == 0 || _energy.ParentItemId == null)
                _Command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = _energy.ParentItemId;

            // IsMajor
            _Command.Parameters.Add("@IsMajor", SqlDbType.Bit).Value = _energy.IsMajor;

            // LanguageCode
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = _energy.LanguageCode;

            #endregion

            try
            {
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return true;
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return false;
            }

        }

        public static bool UpdateEnergyConsumptionItemValues(EnergyConsumptionDevelopmentItemValue _energy)
        {
            #region Command Text

            _Command.CommandText = @" BEGIN TRANSACTION
                                      DELETE FROM EnergyConsumptionDevelopment WHERE ItemId= @ItemId AND CountryId = @CountryId;
                                      INSERT INTO EnergyConsumptionDevelopment ";

            string ColumnNames = "ItemId,CountryId,";
            string Values = "@ItemId,@CountryId,";
            foreach (ItemValue _item in _energy.ItemValues)
            {
                ColumnNames += "[" + _item.Year.ToString() + "],";
                Values += _item.Value == "" ? "NULL," : "'" + _item.Value.ToString() + "',";
            }
            ColumnNames = ColumnNames.Remove(ColumnNames.Length - 1);
            Values = Values.Remove(Values.Length - 1);

            _Command.CommandText += "(" + ColumnNames + ") VALUES (" + Values + ")";

            _Command.CommandText += @" IF @@ERROR <> 0
                                            ROLLBACK
                                        COMMIT";

            _Command.CommandType = CommandType.Text;

            #endregion

            #region Parameters

            _Command.Parameters.Clear();
            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = _energy.ItemId;
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = _energy.CountryId;

            #endregion

            try
            {
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return true;
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return false;
            }
        }

        #endregion

        #region Insert

        public static bool InsertMasterEnergyConsumptions(EnergyConsumptionDevelopmentItemValue _energy)
        {
            #region Stored procedure
            _Command.CommandText = "SP_InsertMasterEnergyConsumptions";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Paramaters

            // Clear All previous parameters if exist
            _Command.Parameters.Clear();

            #region English Details

            // ItemName
            _Command.Parameters.Add("@EnglishItemName", SqlDbType.NVarChar).Value = _energy.EnglishItemName;

            // Description
            _Command.Parameters.Add("@EnglishDescription", SqlDbType.NVarChar).Value = _energy.EnglishDescription;

            #endregion

            #region French Details

            // ItemName
            _Command.Parameters.Add("@FrenchItemName", SqlDbType.NVarChar).Value = _energy.FrenchItemName;

            // Description
            _Command.Parameters.Add("@FrenchDescription", SqlDbType.NVarChar).Value = _energy.FrenchDescription;

            #endregion

            #region Arabic Details

            // ItemName
            _Command.Parameters.Add("@ArabicItemName", SqlDbType.NVarChar).Value = _energy.ArabicItemName;

            // Description
            _Command.Parameters.Add("@ArabicDescription", SqlDbType.NVarChar).Value = _energy.ArabicDescription;

            #endregion

            // ParentItemId
            if (_energy.ParentItemId == 0 || _energy.ParentItemId == null)
                _Command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = _energy.ParentItemId;

            // IsMajor
            _Command.Parameters.Add("@IsMajor", SqlDbType.Bit).Value = _energy.IsMajor;

            // LanguageCode
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = _energy.LanguageCode;

            #endregion

            try
            {
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return true;
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return false;
            }


        }

        #endregion

        #region Delete

        public static bool DeleteMasterEnergyConsumptions(int ItemId)
        {
            #region Stored procedure
            _Command.CommandText = "SP_DeleteEnergyConsumptionDevelopmentItem";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            // Clear all previous parameters
            _Command.Parameters.Clear();

            // add ItemId Param
            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

            try
            {
                OpenConnection();

                _Command.ExecuteNonQuery();

                CloseConnection();
                return true;
            }
            catch (Exception exc)
            {
                CloseConnection();

                return false;
            }

        }
        #endregion

        #endregion

        #region Private Methods

        #endregion

    }
}
